package com.authine.cloudpivot.controller;

/**
 * @author pandodo
 * @date 2023年06月28日 15:37
 */

import com.authine.cloudpivot.foundation.orm.api.dml.JPANativeQuery;
import com.authine.cloudpivot.web.api.handler.CustomizedOrigin;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

/**
 * 定制sql查询功能Controller.
 */
@Api(tags = "运行时::列表::定制列表查询")
@RestController
@RequestMapping("/api/sql/query")
@Slf4j
@CustomizedOrigin(level = 1)
public class QuerySqlController {

    @Autowired
    private JPANativeQuery jpaNativeQuery;

    @GetMapping("/getDeptById")
    public List<Map<String, Object>> getDeptById(@RequestParam("id")String id){
        log.info("传入id 查询 办事处 :{}",id);

        String finalQuerySql = String.format("select id, REPLACE(name,'长沙市开福区','') name from h_org_department where parentid = '2c9a8e6488e8fca60188e912ee421f97'\n" +
                "and deleted=0 and querycode = (select SUBSTRING_INDEX(queryCode,'#',4)  from h_org_department where id = '%s' and deleted=0 \n" +
                ")",id);
        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    @GetMapping("/getDeptByParentId")
    public List<Map<String, Object>> getDeptByParentId(@RequestParam("parentId")String parentId){
        log.info("传入id 查询 查询下级机构 :{}",parentId);

        String finalQuerySql = String.format("select id,name from h_org_department where deleted=0 and parentId = '%s' ",parentId);
        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    @GetMapping("/getStreet")
    public List<Map<String, Object>> getStreet(){
        log.info("查询街道");

        String finalQuerySql = "select id, REPLACE(name,'长沙市开福区','') name from h_org_department where parentid = '2c9a8e6488e8fca60188e912ee421f97' " +
                "and deleted=0";

        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }



    @GetMapping("/getCommunity")
    public List<Map<String, Object>> getCommunity(@RequestParam("parentId")String parentId,@RequestParam("id")String id){
        log.info("传入parenid:{} ，id:{} 查询社区",parentId,id);

        String finalQuerySql = String.format("select id,name ,querycode from h_org_department where parentid ='%s' \n" +
                "and deleted=0 and querycode = (select SUBSTRING_INDEX(queryCode,'#',5) from h_org_department where id ='%s' and deleted=0 \n" +
                ")",parentId,id);

        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }


    @GetMapping("/getReseau")
    public List<Map<String, Object>> getReseau(@RequestParam("parentId")String parentId,@RequestParam("id")String id){
        log.info("传入parenid:{} ，id:{} 查询社区",parentId,id);

        String finalQuerySql = String.format("select id,name ,querycode from h_org_department where parentid ='%s' \n" +
                "and deleted=0 and querycode = (select SUBSTRING_INDEX(queryCode,'#',6) from h_org_department where id ='%s' and deleted=0 \n" +
                ")",parentId,id);

        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }



    @RequestMapping("/getSql")
    public List<Map<String,Object>> getSql(@RequestParam("sql") String sql){
        log.info("sql = :{}",sql);

        return this.jpaNativeQuery.getList(sql, null, null);
    }


    /** 
        未移交道路-查询明细
    */
    @GetMapping("/getDLList")
    public List<Map<String, Object>> getDLList(@RequestParam("dldj") String dldj, @RequestParam("tjzt") String tjzt, @RequestParam("tjjd") String tjjd, @RequestParam("dlyhcd") String dlyhcd,@RequestParam("emergencyType") String emergencyType) {
        log.info("getDLList传入道路等级dldj :{}", dldj);
        log.info("getDLList传入推进状态tjzt :{}", tjzt);
        log.info("getDLList传入推进阶段tjjd :{}", tjjd);
        log.info("getDLList传入道路隐患程度dlyhcd :{}", dlyhcd);
        log.info("getDLList传入紧急类型emergencyType:{}", emergencyType);
        String finalQuerySql = String.format("select t1.sequenceNo,t1.id , \n" +
                "concat(ROW_NUMBER() OVER (ORDER BY FIELD(ifnull(dlyhcd,''),'重大','较大','一般','无','')),'、' ,IFNULL(t2.name,''),'-',IFNULL(jsdw,''),'-',IFNULL(ShortText1705054543101,''),'-',IFNULL(Radio1705054689956,'') ) as name \n" +
                ", t2.id jdid,t2.name jdname,t1.dlyhcd ,concat(ROW_NUMBER() OVER (ORDER BY FIELD(ifnull(dlyhcd,''),'重大','较大','一般','无','')),'、' ,IFNULL(t2.name,''),'-',IFNULL(ShortText1705054543101,'')) as gdname \n"+
                ",case when t1.jahqk = '通过' then 1 else 0 end as jahqkIstrue \n" +
                ",case when t1.trsy = '是' then 1 else 0 end as trsyIstrue \n" +
                ",case when t1.jgys = '是' then 1 else 0 end as jgysIstrue \n" +
                ",case when t1.yshqk = '通过' then 1 else 0 end as yshqkIstrue \n" +
                ",case when t1.yjhqk = '通过' then 1 else 0 end as yjhqkIstrue \n" +
                ",case when ( t1.jahqk = '通过' and t1.trsy = '是' and  t1.jgys = '是' and t1.yshqk = '通过'and t1.yjhqk = '通过' ) then 1 when (emergencyType  ='紧急' ) then 1 else 0 end as yjwjIstrue \n" +
                ",t1.jahqk ,t1.trsy ,t1.jgys,t1.yshqk , t1.yjhqk ,ifnull(jah_deficiency,'否') as jah_deficiency,ifnull(Radio1714484430704,'否') as Radio1714484430704,ifnull(ysh_deficiency,'否') as ysh_deficiency,ifnull(yjh__deficiency,'否') as yjh__deficiency from iknn4_byjqd t1 left join h_org_department t2 \n" +
                "on t1.ssjd = t2.id where sequenceStatus ='COMPLETED' \n" );
        if(!"全部".equals(dldj)){
            finalQuerySql += String.format ("and t1.dldj ='%s' \n ", dldj );
        }
        if(!"全部".equals(tjzt)){
            finalQuerySql += String.format ("and t1.Radio1705054689956 ='%s' \n ", tjzt );
        }
        if("交安会".equals(tjjd)){
            finalQuerySql += String.format ("and ifnull(t1.jahqk,'未通过') ='未通过'  \n " );
        }else if("验收会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and ifnull(t1.yshqk,'未通过') ='未通过'  \n " );
        }else if("移交会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and ifnull(t1.yjhqk,'未通过') ='未通过'  \n " );
        }else if("移交完结".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and t1.yjhqk='通过'  \n " );
        }
        if(!"全部".equals(dlyhcd)){
            finalQuerySql += String.format ("and ifnull(t1.dlyhcd,'无')  ='%s' \n ", dlyhcd );
        }
        if("紧急".equals(emergencyType)){
            finalQuerySql +=  String.format ("and emergencyType  ='%s' \n ", emergencyType );
        }
        finalQuerySql += String.format(" order by  FIELD(ifnull(dlyhcd,''),'重大','较大','一般','无','')\n ");


        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }



    /** 
        未移交道路-查询隐患程度
    */
    @GetMapping("/getDLyhcd")
    public List<Map<String, Object>> getDLyhcd(@RequestParam("dldj") String dldj, @RequestParam("tjzt") String tjzt, @RequestParam("tjjd") String tjjd, @RequestParam("dlyhcd") String dlyhcd,@RequestParam("emergencyType") String emergencyType) {
        log.info("getDLyhcd传入道路等级dldj :{}", dldj);
        log.info("getDLyhcd传入推进状态tjzt :{}", tjzt);
        log.info("getDLyhcd传入推进阶段tjjd :{}", tjjd);
        log.info("getDLyhcd传入道路隐患程度dlyhcd :{}", dlyhcd);
        log.info("getDLList传入紧急类型emergencyType:{}", emergencyType);

        String finalQuerySql = String.format("select \n" +
                " sum(case when ifnull( dlyhcd,'无') ='无' then 1 else 0 end ) wCnt, \n" +
                " sum(case when   dlyhcd= '一般' then 1 else 0 end ) ybCnt, \n" +
                " sum(case when   dlyhcd= '较大' then 1 else 0 end ) jdCnt, \n"+
                " sum(case when   dlyhcd= '重大' then 1 else 0 end ) zdCnt \n" +
                " ,sum(1) total \n" +
                " from iknn4_byjqd t1 where sequenceStatus ='COMPLETED' \n" );
        if(!"全部".equals(dldj)){
            finalQuerySql += String.format ("and t1.dldj ='%s' \n ", dldj );
        }
        if(!"全部".equals(tjzt)){
            finalQuerySql += String.format ("and t1.Radio1705054689956 ='%s' \n ", tjzt );
        }
        if("交安会".equals(tjjd)){
            finalQuerySql += String.format ("and ifnull(t1.jahqk,'未通过') ='未通过'  \n " );
        }else if("验收会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and ifnull(t1.yshqk,'未通过') ='未通过'  \n " );
        }else if("移交会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and ifnull(t1.yjhqk,'未通过') ='未通过'  \n " );
        }else if("移交完结".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and t1.yjhqk='通过'  \n " );
        }
        if(!"全部".equals(dlyhcd)){
            finalQuerySql += String.format ("and ifnull(t1.dlyhcd,'无')  ='%s' \n ", dlyhcd );
        }
        if("紧急".equals(emergencyType)){
            finalQuerySql +=  String.format ("and emergencyType  ='%s' \n ", emergencyType );
        }
        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    /** 
        未移交道路-查询道路等级
    */
    @GetMapping("/getDLLv")
    public List<Map<String, Object>> getDLLv(@RequestParam("dldj") String dldj, @RequestParam("tjzt") String tjzt, @RequestParam("tjjd") String tjjd, @RequestParam("dlyhcd") String dlyhcd,@RequestParam("emergencyType") String emergencyType) {
        log.info("getDLLv传入道路等级dldj :{}", dldj);
        log.info("getDLLv传入推进状态tjzt :{}", tjzt);
        log.info("getDLLv传入推进阶段tjjd :{}", tjjd);
        log.info("getDLLv传入道路隐患程度dlyhcd :{}", dlyhcd);
        log.info("getDLList传入紧急类型emergencyType:{}", emergencyType);

        String finalQuerySql = String.format(" select  sum(case when ifnull( dldj,'其他') ='其他' then 1 else 0 end ) qtCnt, \n" +
                " sum(case when   dldj= '主干道' then 1 else 0 end ) zgdCnt, \n" +
                " sum(case when   dldj= '次干道' then 1 else 0 end ) cgdCnt, \n" +
                " sum(case when   dldj= '支路' then 1 else 0 end ) zlCnt, \n"+
                " sum(case when   dldj= '农村道路' then 1 else 0 end ) ncdlCnt \n" +
                " ,sum(1) total \n" +
                " from iknn4_byjqd t1 where sequenceStatus ='COMPLETED' \n" );
        if(!"全部".equals(dldj)){
            finalQuerySql += String.format ("and t1.dldj ='%s' \n ", dldj );
        }
        if(!"全部".equals(tjzt)){
            finalQuerySql += String.format ("and t1.Radio1705054689956 ='%s' \n ", tjzt );
        }
        if("交安会".equals(tjjd)){
            finalQuerySql += String.format ("and ifnull(t1.jahqk,'未通过') ='未通过'  \n " );
        }else if("验收会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and ifnull(t1.yshqk,'未通过') ='未通过'  \n " );
        }else if("移交会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and ifnull(t1.yjhqk,'未通过') ='未通过'  \n " );
        }else if("移交完结".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and t1.yjhqk='通过'  \n " );
        }
        if(!"全部".equals(dlyhcd)){
            finalQuerySql += String.format ("and ifnull(t1.dlyhcd,'无')  ='%s' \n ", dlyhcd );
        }
        if("紧急".equals(emergencyType)){
            finalQuerySql +=  String.format ("and emergencyType  ='%s' \n ", emergencyType );
        }
        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }


    /** 
        未移交道路-查询推进阶段
    */
    @GetMapping("/getDLtjjd")
    public List<Map<String, Object>> getDLtjjd(@RequestParam("dldj") String dldj, @RequestParam("tjzt") String tjzt, @RequestParam("tjjd") String tjjd, @RequestParam("dlyhcd") String dlyhcd,@RequestParam("emergencyType") String emergencyType) {
        log.info("getDLtjjd传入道路等级dldj :{}", dldj);
        log.info("getDLtjjd传入推进状态tjzt :{}", tjzt);
        log.info("getDLtjjd传入推进阶段tjjd :{}", tjjd);
        log.info("getDLList传入道路隐患程度dlyhcd :{}", dlyhcd);
        log.info("getDLList传入紧急类型emergencyType:{}", emergencyType);

        String finalQuerySql = String.format(" select sum(case when ifnull( jahqk,'未通过') ='未通过' then 1 else 0 end ) jahCnt, \n" +
                " sum(case when (  jahqk ='通过' and ifnull(t1.yshqk,'未通过') ='未通过' )  then 1 else 0 end ) yshCnt, \n" +
                " sum(case when (jahqk ='通过' and t1.yshqk='通过' and ifnull(t1.yjhqk,'未通过') ='未通过') then 1 else 0 end ) yjh, \n" +
                " sum(case when (jahqk ='通过' and t1.yshqk='通过' and t1.yjhqk='通过') then 1 else 0 end ) yswjCnt , \n"+
                " sum(case when   dldj= '农村道路' then 1 else 0 end ) ncdlCnt \n" +
                " ,sum(1) total \n" +
                " from iknn4_byjqd t1 where sequenceStatus ='COMPLETED' \n" );
        if(!"全部".equals(dldj)){
            finalQuerySql += String.format ("and t1.dldj ='%s' \n ", dldj );
        }
        if(!"全部".equals(tjzt)){
            finalQuerySql += String.format ("and t1.Radio1705054689956 ='%s' \n ", tjzt );
        }
        if("交安会".equals(tjjd)){
            finalQuerySql += String.format ("and ifnull(t1.jahqk,'未通过') ='未通过'  \n " );
        }else if("验收会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and ifnull(t1.yshqk,'未通过') ='未通过'  \n " );
        }else if("移交会".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and ifnull(t1.yjhqk,'未通过') ='未通过'  \n " );
        }else if("移交完结".equals(tjjd)){
            finalQuerySql += String.format ("and jahqk ='通过' and t1.yshqk='通过' and t1.yjhqk='通过'  \n " );
        }
        if(!"全部".equals(dlyhcd)){
            finalQuerySql += String.format ("and ifnull(t1.dlyhcd,'无')  ='%s' \n ", dlyhcd );
        }
        if("紧急".equals(emergencyType)){
            finalQuerySql +=  String.format ("and emergencyType  ='%s' \n ", emergencyType );
        }
        log.info("sql = :{}",finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }
}
